package com.li.demo;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.List;

/**
 * 导入导出Excel
 */
public class ExcelUtils {

    /**
     * @param outputStream 输出流
     * @param dataList     数据
     * @deprecated 导出Excel
     */
    public static void buildXLSX(List<String[]> dataList, OutputStream outputStream) {
        try {
            // 声明一个Excel工作簿
            XSSFWorkbook workBook = null;
            workBook = new XSSFWorkbook();
            // 生成一个工作表
            XSSFSheet sheet = workBook.createSheet();
            workBook.setSheetName(0, "info");//工作表名为info,并且加入到工作簿中，索引为0

            //插入需导出的数据
            for (int i = 0; i < dataList.size(); i++) {
                XSSFRow row = sheet.createRow(i);//创建行
                String[] oneRowData = dataList.get(i);
                for (int j = 0; j < oneRowData.length; j++) {
                    row.createCell(j).setCellValue(oneRowData[j]);//创建列，并且给列赋值
                }
            }
            //写入输出流
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取Excel数据
     */
    public void readExcel() throws IOException {
        //获取文件输入流
        InputStream is = new FileInputStream("D:\\tmp\\新建 XLSX 工作表.xlsx");
        //生成一个Excel
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        //获得Excel第一个工作簿
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        //行和列
        XSSFRow row = null;
        XSSFCell cell = null;
        //遍历行
        for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取行
            row = sheet.getRow(i);
            //判断空行
            if (row == null) {
                continue;
            }
            //遍历列
            Object[] objects = new Object[row.getLastCellNum()];
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);//获取单元格
                cell.setCellType(CellType.STRING);//转单元格内容为字符串
                if (cell == null) {
                    continue;
                }
                System.out.print(cell.getStringCellValue());//获取字符串类型的单元格中的值
            }
            System.out.println();
        }
    }
}
